package com.tjsinfo.tjsoa.articles.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;

import com.tjsinfo.tjsoa.articles.vo.TIndexArticles;

public class ArticlesDaoJdbc extends JdbcDaoSupport implements IArticlesDao{


//	public String save(final TIndexArticles tjb) {
//		if (tjb.getId() == null) {
//			
//		//	KeyHolder keyHolder = new GeneratedKeyHolder();
//			final String sql = "insert into dbo.t_articles(id,cid,title,pic,userId,createDate,state,resume,content,auditMan,auditIdea,auditing_sign, wenzhang) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
//			String  uuidstr = UUID.randomUUID().toString();
//			uuidstr = uuidstr.substring(0,8)+uuidstr.substring(9,13)+uuidstr.substring(14,18)+uuidstr.substring(19,23)+uuidstr.substring(24); 
//			final String uuid = 	uuidstr;
//			getJdbcTemplate().update(new PreparedStatementCreator() {
//				public java.sql.PreparedStatement createPreparedStatement(
//						Connection conn) throws SQLException {
//					int i = 0;
//					java.sql.PreparedStatement ps = conn.prepareStatement(sql,new String[] { "id" });

				//	ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//					ps.setString(++i, uuid);
//					ps.setString(++i,tjb.getCid().toString());
//					ps.setString(++i,tjb.getTitle());
//					ps.setString(++i,tjb.getPic());
//					ps.setString(++i,tjb.getUserId());
//					ps.setString(++i,tjb.getCreateDate());
//					ps.setString(++i,tjb.getState());
//					ps.setString(++i,tjb.getResume());
//					ps.setString(++i,tjb.getContent());
//					ps.setString(++i,tjb.getAuditMan());
//					ps.setString(++i,tjb.getAuditIdea());
//					ps.setString(++i,tjb.getAuditing_sign());
//					ps.setString(++i,tjb.getWenzhang());

//					return ps;					
//				}
//			});
//
//			
//			return uuid;
//		} else {
//			getJdbcTemplate()
//					.update(
//							"update dbo.t_articles set id = ?,cid = ?, title = ?, pic = ?, userId = ?, createDate = ?, state = ?, resume = ?," +
//					" content = ?, auditMan = ?, auditIdea = ?, auditing_sign = ?,wenzhang = ? where id = ?",
//							new Object[] { tjb.getId(),tjb.getCid(), tjb.getTitle(),tjb.getPic(),
//									 tjb.getUserId(), tjb.getCreateDate(),tjb.getState(),
//									 tjb.getResume(), tjb.getContent(),tjb.getAuditMan(),
//									 tjb.getAuditIdea(),tjb.getAuditing_sign(),tjb.getWenzhang(),
//									 tjb.getId() });
//			
//			return "0";
//		}
//		
//	}
	private TIndexArticles help(ResultSet rs,TIndexArticles article) throws SQLException{
//		article.setId(new String(rs.getString("id")));
//		article.setCid(rs.getString("cid"));
//		article.setTitle(rs.getString("title"));
//		article.setPic(rs.getString("pic"));
//		article.setUserId(rs.getString("userId"));
//		article.setCreateDate(rs.getString("createDate"));
//		article.setState(rs.getString("state"));
//		article.setAuditMan(rs.getString("auditMan"));
//		article.setContent(rs.getString("content"));
//		article.setResume(rs.getString("resume"));
//		article.setAuditIdea(rs.getString("auditIdea"));
//		article.setAuditing_sign(rs.getString("auditing_sign"));
//		article.setWenzhang(rs.getString("wenzhang"));
		
		return article;
	}

		
	public void removeArticle(String articleId) {		
		getJdbcTemplate().update("delete from dbo.t_articles where id = ? ",new Object[]{ articleId});
	} 
	
	public List findAll() {
		List list = new ArrayList();
		list = new BzzfQuery(getDataSource()).execute();
		return list;
	}

	public List findByProperty(String propertyName, String value) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_index_articles where " + propertyName
				+ " = ? order by showOrder desc,id desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value, types)
				.execute(new Object[] { value });
		return list;
	}

	public List findByTwoProperty(String propertyName1, String value1,
			String propertyName2, String value2) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_articles where " + propertyName1
				+ " = ? and " + propertyName2 + "= ? order by state desc,createDate desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types)
				.execute(new Object[] { value1, value2 });
		return list;
	}
	
	public List queryResult(String propertyName1, String value1,
			String propertyName2, String value2) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_articles where " + propertyName1
				+ " = ? and " + propertyName2 + " like ? ";
		System.out.println("select * from dbo.t_articles where " + propertyName1
				+ " = "+value1+" and " + propertyName2  + " like "+value2);
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types)
				.execute(new Object[] { value1, value2 });
		return list;
	}

	public List findByThreeProperty(String propertyName1, String value1,
			String propertyName2, String value2, String propertyName3,
			String value3) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_articles where " + propertyName1
				+ " = ? and " + propertyName2 + "= ? and " + propertyName3 + "= ? order by state desc,createDate desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types, value3, types)
				.execute(new Object[] { value1, value2, value3 });
		return list;
	}


	
	public List findList(String value, String value1 ){
	List list = new ArrayList();
	String sql = "select  top "+ value+" * from dbo.t_articles where cid "
			+ " = ? and auditing_sign = 1 order by state desc,id desc";
	int types = Types.VARCHAR;
	list = new BzzfQuery(getDataSource(), sql, value1, types)
			.execute(new Object[] { value1 });
	return list;

	}
	//
	public List topNandNot(String top, String whereValue,String notValue ){
		List list = new ArrayList();
		String sql = "select  top "+ top+" * from dbo.t_articles where cid = ? and pic != ? and auditing_sign = 1 ORDER BY  createDate DESC";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, whereValue, types,notValue,types)
				.execute(new Object[] { whereValue,notValue });
		return list;

		}
	
	class BzzfQuery extends MappingSqlQuery {

		BzzfQuery(DataSource ds) {
			super(ds, "select * from dbo.t_articles order by state desc,id desc");
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column, int types) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column, types));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2, String column3, int types3) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			super.declareParameter(new SqlParameter(column3, types3));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2, String column3, int types3,
				String column4, int types4) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			super.declareParameter(new SqlParameter(column3, types3));
			super.declareParameter(new SqlParameter(column4, types4));
			compile();
		}

		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			TIndexArticles hbgb = new TIndexArticles();
			hbgb = help(rs, hbgb);
			return hbgb;
		}
	}



}
